﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace PowerPivotUpdater
{
    public static class ExcelServiceManager
    {

        public static string[] GetBookConnections(string webSite, string excelPath)
        {
            List<string> lst = new List<string>();
            string excelWSAddress = "";
            if (webSite.Substring(webSite.Length - 1) == "/")
            {
                excelWSAddress = webSite + "_vti_bin/excelservice.asmx";
            }
            else
            {
                excelWSAddress = webSite + "/_vti_bin/excelservice.asmx";

            }
            try
            {
                var xlservice = new excelsvc.ExcelService();
                xlservice.Url = excelWSAddress;
                xlservice.Credentials = System.Net.CredentialCache.DefaultCredentials;
                excelsvc.Status[] outStatus;
                string sessionId = xlservice.OpenWorkbook(excelPath , System.Globalization.CultureInfo.CurrentUICulture.Name, System.Globalization.CultureInfo.CurrentCulture.Name, out outStatus);
                var conns = xlservice.GetWorkbookConnections(sessionId, out outStatus);
                foreach (var s in conns)
                {
                    lst.Add(s.ToString());
                }
                outStatus = xlservice.CloseWorkbook(sessionId);
                return lst.ToArray();
            }
            catch (Exception ex)
            {
                return new string[] { "Unable to open the workbook:" + ex.Message  };
            }

        }

        public static bool RefreshWorkbook(string webSite, string excelPath, string connection)
        {
            string excelWSAddress = "";
            if (webSite.Substring(webSite.Length - 1) == "/")
            {
                excelWSAddress = webSite + "_vti_bin/excelservice.asmx";
            }
            else
            {
                excelWSAddress = webSite + "/_vti_bin/excelservice.asmx";
            }
            var xlservice = new excelsvc.ExcelService();
            xlservice.Url = excelWSAddress;
            xlservice.Credentials  = System.Net.CredentialCache.DefaultCredentials;
            excelsvc.Status[] outStatus;
            string sessionId = xlservice.OpenWorkbook (excelPath ,  System.Globalization.CultureInfo.CurrentUICulture.Name, System.Globalization.CultureInfo.CurrentCulture.Name, out outStatus);
            var conns = xlservice.GetWorkbookConnections(sessionId, out outStatus);
            foreach (var s in conns)
            {
                if (s.ToLower() == connection.ToLower())
                {
                    outStatus = xlservice.Refresh(sessionId, s);
                    outStatus = xlservice.CloseWorkbook(sessionId);
                    return true;
                }

            }
            outStatus = xlservice.CloseWorkbook(sessionId);
            return false;
        }

        public static string[] GetSiteURL(string ExcelWorkbookURL)
        {
            string st = ExcelWorkbookURL;
            List<string> lst = new List<string>();
            bool salida = false;
            while (!salida) {
                int i = st.LastIndexOf("/");
                if (i < 7)
                    salida = true;
                else
                {
                    if (st[i - 1] == '/')
                        salida = true;
                    else
                    {
                        lst.Add(st.Substring(0, i ));
                        st = st.Substring(0, i );
                    }
                
                }
            }
            return lst.ToArray();
        }
    }
}
